package limousine.util;

import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.NamingException;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

import java.net.URL;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import limousine.vo.Users;
import limousine.vo.Module;
import limousine.vo.UserControl;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class Service {

    private Func func = new Func();
    /*
    public DataSource getDataSource() {
        DataSource ds = null;
        try {
            InitialContext cxt;
            cxt = new InitialContext();
            if (cxt == null) {
                System.out.println("NULL of InitialContext");
            }
            ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/" + Token.DATASOURCE_NAME);
            if (ds == null) {
                System.out.println("NULL of DataSource");
            }
        } catch (NamingException e) {
            System.out.println("Get DataSource " + e.toString());
        }
        return ds;
    }*/
    private ServletContext servletContext = null;

    public void setServletContext(ServletContext servletContext) {
        this.servletContext = servletContext;
    }
    
    
    
    public ServletContext getServletContext() {
        return this.servletContext;
    }
    
    public DataSource getDataSource() {
        DataSource ds = null;
        try {
            if (ds == null) {
            ds = (DataSource) WebApplicationContextUtils.getWebApplicationContext(
                    this.getServletContext()).getBean("dataSource");
            }
        } catch (Exception e) {
            System.out.println("Get DataSource " + e.toString());
        }
        return ds;
    }

    public void removeMsgSession(HttpSession session) {
        
        session.removeAttribute(Token.MSG_ERROR);
        session.removeAttribute(Token.MSG_USER);


    }

    public String getCondition(int i) {
        if (i > 0) {
            return " and ";
        } else {
            return " where ";
        }
    }

    public void removeMsgAttribute(HttpServletRequest request, HttpSession session, String refStr) {
        String ref = request.getParameter("ref");
        ref = (ref == null) ? "" : ref.trim();
        session.removeAttribute(Token.MSG_ERROR);
        if (ref.equals("")) {
            session.removeAttribute(Token.MSG_USER);
            return;
        }
        /*
        if(!ref.equals(refStr)){
        session.removeAttribute(Token.MSG_ERROR);
        session.removeAttribute(Token.MSG_USER);
        return;
        } */
    }

    public List<UserControl> getUserControl(DataSource ds, String role_id) {
        List<UserControl> list = new ArrayList<UserControl>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select s.id,s.name,s.detail ");
            sb.append("from role_systems rs,systems s ");
            sb.append("where rs.role_id='" + role_id + "' and rs.systems_id=s.id and s.enable='1' order by s.name ");

            System.out.println("getUserControl: " + sb.toString());

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                UserControl o = new UserControl();
                o.setId(rs.getString(1));
                o.setName(rs.getString(2));
                o.setDetail(rs.getString(3));

                List<Module> m = getSystemModule(ds, o.getId());
                o.setModule(m);

                if (m.size() > 0) {
                    list.add(o);
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Module> getSystemModule(DataSource ds, String systems_id) {
        List<Module> list = new ArrayList<Module>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select m.id,m.name,m.detail,m.link from module m ");
            sb.append("where m.systems_id='" + systems_id + "' and m.enable='1' order by m.name");
            System.out.println("getSystemModule: " + sb.toString());

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                Module o = new Module();
                o.setId(rs.getString(1));
                o.setName(rs.getString(2));
                o.setDetail(rs.getString(3));
                o.setLink(rs.getString(4));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> setListHaveSelectOne(List<SelectItem> listParm, String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("");//don't put 0
        si.setValue("--- Select One ---");
        list.add(si);
        for (int i = 0; i < listParm.size(); i++) {
            si = new SelectItem();
            si.setKey(listParm.get(i).getKey());
            si.setValue(listParm.get(i).getValue());
            if (listParm.get(i).getKey().equals(currentID.trim())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        listParm = list;
        return listParm;
    }

    public List<SelectItem> setListHaveSelectDay(List<SelectItem> listParm, String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("");//don't put 0
        si.setValue("--- Select Day ---");
        list.add(si);
        for (int i = 0; i < listParm.size(); i++) {
            si = new SelectItem();
            si.setKey(listParm.get(i).getKey());
            si.setValue(listParm.get(i).getValue());
            if (listParm.get(i).getKey().equals(currentID.trim())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        listParm = list;
        return listParm;
    }

    public List<SelectItem> setListHaveAll(List<SelectItem> listParm, String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("");//don't put 0
        si.setValue("--- All ---");
        list.add(si);
        for (int i = 0; i < listParm.size(); i++) {
            si = new SelectItem();
            si.setKey(listParm.get(i).getKey());
            si.setValue(listParm.get(i).getValue());
            if (listParm.get(i).getKey().equals(currentID.trim())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        listParm = list;
        return listParm;
    }

    public List<SelectItem> setListHaveOptional(List<SelectItem> listParm, String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("");//don't put 0
        si.setValue("--- Optional  ---");
        list.add(si);
        for (int i = 0; i < listParm.size(); i++) {
            si = new SelectItem();
            si.setKey(listParm.get(i).getKey());
            si.setValue(listParm.get(i).getValue());
            if (listParm.get(i).getKey().equals(currentID.trim())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        listParm = list;
        return listParm;
    }

    public List<SelectItem> setListHaveNoUse(List<SelectItem> listParm, String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("");//don't put 0
        si.setValue("--- No Use  ---");
        list.add(si);
        for (int i = 0; i < listParm.size(); i++) {
            si = new SelectItem();
            si.setKey(listParm.get(i).getKey());
            si.setValue(listParm.get(i).getValue());
            if (listParm.get(i).getKey().equals(currentID.trim())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        listParm = list;
        return listParm;
    }

    public List<SelectItem> getGenderList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("m");
        si.setValue("Man");
        if (currentID.equals("m")) {
            si.setSelect(true);
        }
        list.add(si);
        si = new SelectItem();
        si.setKey("w");
        si.setValue("Women");
        if (currentID.equals("w")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }

    public List<SelectItem> getGenderList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        si = new SelectItem();
        si.setKey("m");
        si.setValue("Man");
        if (currentID.equals("m")) {
            si.setSelect(true);
        }
        list.add(si);

        si = new SelectItem();
        si.setKey("w");
        si.setValue("Women");
        if (currentID.equals("w")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }

    public List<SelectItem> getGenderThList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        si = new SelectItem();
        si.setKey("m");
        si.setValue("ชาย");
        if (currentID.equals("m")) {
            si.setSelect(true);
        }
        list.add(si);

        si = new SelectItem();
        si.setKey("w");
        si.setValue("หญิง");
        if (currentID.equals("w")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }
   
    public List<SelectItem> getEnableList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        si = new SelectItem();
        si.setKey("1");
        si.setValue("On");
        if (currentID.equals("1")) {
            si.setSelect(true);
        }
        list.add(si);
        si = new SelectItem();
        si.setKey("0");
        si.setValue("Off");
        if (currentID.equals("0")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }

    public List<SelectItem> getStatusList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        si = new SelectItem();
        si.setKey("1");
        si.setValue("Online");
        if (currentID.equals("1")) {
            si.setSelect(true);
        }
        list.add(si);

        si = new SelectItem();
        si.setKey("0");
        si.setValue("Offline");
        if (currentID.equals("0")) {
            si.setSelect(true);
        }
        list.add(si);

        return list;
    }

    public List<SelectItem> getDateList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }
        for (int i = 1; i <= 31; i++) {
            si = new SelectItem();
            if (i < 10) {
                si.setKey("0" + i);
                si.setValue("0" + i);
            } else {
                si.setKey("" + i);
                si.setValue("" + i);
            }
            if (currentID.equals(si.getKey())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getMonthList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        String[] month = {"มกราคม", "กุมภาพันธ์", "มีนาคม", "เมษายน", "พฤษภาคม", "มิถุนายน", "กรกฎาคม", "สิงหาคม",
            "กันยายน", "ตุลาคม", "พฤศจิกายน", "ธันวาคม",};
        for (int i = 1; i <= month.length; i++) {
            si = new SelectItem();
            if (i < 10) {
                si.setKey("0" + i);
                si.setValue(month[i - 1]);
            } else {
                si.setKey("" + i);
                si.setValue(month[i - 1]);
            }
            if (currentID.equals(si.getKey())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getYearList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        for (int i = 2480; i < 2554; i++) {
            si = new SelectItem();
            si.setKey("" + i);
            si.setValue("" + i);
            if (currentID.equals(si.getKey())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getEnableList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("0");
        si.setValue("Off");
        if (currentID.equals("0")) {
            si.setSelect(true);
        }
        list.add(si);
        si = new SelectItem();
        si.setKey("1");
        si.setValue("On");
        if (currentID.equals("1")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }

    public List<SelectItem> getYesNoList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        si.setKey("1");
        si.setValue("Yes");
        if (currentID.equals("1")) {
            si.setSelect(true);
        }
        list.add(si);
        si = new SelectItem();
        si.setKey("0");
        si.setValue("No");
        if (currentID.equals("0")) {
            si.setSelect(true);
        }
        list.add(si);
        return list;
    }

    public List<SelectItem> getHtmlTargetList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }

        si = new SelectItem();
        si.setKey("_blank");
        si.setValue("_blank");
        if (currentID.equals("_blank")) {
            si.setSelect(true);
        }
        list.add(si);
        si = new SelectItem();
        si.setKey("_self");
        si.setValue("_self");
        if (currentID.equals("_self")) {
            si.setSelect(true);
        }
        list.add(si);
        /*
        si = new SelectItem();
        si.setKey("_parent");
        si.setValue("_parent");
        if (currentID.equals("_parent")) {
        si.setSelect(true);
        }
        list.add(si);

        si = new SelectItem();
        si.setKey("_top");
        si.setValue("_top");
        if (currentID.equals("_top")) {
        si.setSelect(true);
        }
        list.add(si);
         */
        return list;
    }

    public List<SelectItem> getEmptyList() {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();

        si = new SelectItem();
        si.setKey("");
        si.setValue("--- All ---");
        list.add(si);
        return list;
    }
    public List<SelectItem> getDayOfWeekList(String currentID) {
        String[] day = {"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"};
        String[] value = {"1", "2", "3", "4", "5", "6", "7"};
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < day.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(value[i]);
            si.setValue(day[i]);
            if (currentID.equals(value[i])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getDayOfMonthList(String currentID) {
        String[] day = {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15",
            "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"
        };
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < day.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(day[i]);
            si.setValue(day[i]);
            if (currentID.equals(day[i])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getEveryDayList(String currentID) {
        String[] day = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15",
            "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"
        };
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < day.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(day[i]);
            si.setValue(day[i]);
            if (currentID.equals(day[i])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getHourList(String currentID) {
        String[] time = {"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15",
            "16", "17", "18", "19", "20", "21", "22", "23"};
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < time.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(time[i]);
            si.setValue(time[i]);
            if (currentID.equals(time[i])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getMinuteList(String currentID) {
        String[] time = {"00", "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15",
            "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33",
            "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53",
            "54", "55", "56", "57", "58", "59"};
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < time.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(time[i]);
            si.setValue(time[i]);
            if (currentID.equals(time[i])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getMidpList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 1; i <= 3; i++) {
            SelectItem si = new SelectItem();
            si.setKey(i + ".0");
            si.setValue(i + ".0");
            if (currentID.equals(si.getKey())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> getSeriesList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        String[] sr = {"s40", "s60", "s80", "s90"};
        for (int i = 0; i < sr.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(sr[i]);
            si.setValue(sr[i]);
            if (currentID.equals(si.getKey())) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }

    public List<SelectItem> setSelectMultiList(List<SelectItem> listParm, String[] selectID) {
        if (selectID.length == 0) {
            return listParm;
        }
        for (int i = 0; i < listParm.size(); i++) {
            for (int j = 0; j < selectID.length; j++) {
                if (listParm.get(i).getKey().equals(selectID[j].trim())) {
                    listParm.get(i).setSelect(true);
                }
            }
        }
        return listParm;
    }

    public List<SelectItem> setSelectList(List<SelectItem> listParm, String selectID) {
        for (int i = 0; i < listParm.size(); i++) {
            if (listParm.get(i).getKey().equals(selectID.trim())) {
                listParm.get(i).setSelect(true);
            }
        }
        return listParm;
    }

    public List<SelectItem> getSystemList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from systems order by name ");
            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getAllRoleList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from role order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getRoleListByPrivilege(DataSource ds, String userRoleId, String selectId, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        if (showSelectAll) {
            SelectItem item = new SelectItem();
            item.setKey("");
            item.setValue("--- All ---");
            list.add(item);
        }

        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from role where id > '" + userRoleId + "' order by id");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                if (item.getKey().equals(selectId)) {
                    item.setSelect(true);
                }

                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getOperationList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from operation order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getProviderList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from provider order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getContentList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from content order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getAppList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from app order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getTransformList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from transform order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getDeployTypeList() {
        List<SelectItem> list = new ArrayList<SelectItem>();
        String[] value = {"J2ME", "Other"};
        for (int i = 0; i < value.length; i++) {
            SelectItem item = new SelectItem();
            item.setKey(i + "");
            item.setValue(value[i]);
            list.add(item);
        }
        return list;
    }

    public List<SelectItem> getDeviceList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,user_agent from device order by user_agent");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getDevisionList(DataSource ds, String selectedId, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All ---");
            list.add(si);
        }
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from dms_division order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                if (item.getKey().trim().equals(selectedId)) {
                    item.setSelect(true);
                }
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getDevisionList2(DataSource ds, String selectedId, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- ทุกฝ่ายงาน ---");
            list.add(si);
        }
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from dms_division order by name");

            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                if (item.getKey().trim().equals(selectedId)) {
                    item.setSelect(true);
                }
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public String[] getDeviceIdListFromAppDevice(DataSource ds, String app_id) {
        String[] id = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select device_id from app_device where app_id='" + app_id.trim() + "' order by device_id");
            ResultSet rs = stmt.executeQuery(sb.toString());

            List<String> l = new ArrayList<String>();
            while (rs.next()) {
                l.add(rs.getString(1).trim());
            }
            rs.close();
            stmt.close();
            conn.close();

            id = new String[l.size()];
            for (int i = 0; i < l.size(); i++) {
                id[i] = l.get(i);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public String[] getDeviceIdListFromDeployDevice(DataSource ds, String deploy_id) {
        String[] id = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select device_id from deploy_device where deploy_id='" + deploy_id.trim() + "' order by device_id");
            ResultSet rs = stmt.executeQuery(sb.toString());

            List<String> l = new ArrayList<String>();
            while (rs.next()) {
                l.add(rs.getString(1).trim());
            }
            rs.close();
            stmt.close();
            conn.close();

            id = new String[l.size()];
            for (int i = 0; i < l.size(); i++) {
                id[i] = l.get(i);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public String[] getDeviceIdListFromContentDevice(DataSource ds, String content_id) {
        String[] id = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select device_id from content_device where content_id='" + content_id.trim() + "' order by device_id");
            ResultSet rs = stmt.executeQuery(sb.toString());

            List<String> l = new ArrayList<String>();
            while (rs.next()) {
                l.add(rs.getString(1).trim());
            }
            rs.close();
            stmt.close();
            conn.close();

            id = new String[l.size()];
            for (int i = 0; i < l.size(); i++) {
                id[i] = l.get(i);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public List<SelectItem> getRoleById(DataSource ds, String id) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from role ");
            sb.append("where id='" + id.trim() + "' ");
            ResultSet rs = stmt.executeQuery(sb.toString());
            if (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<SelectItem> getRoleEmptyList(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();

            String[] id = getRoleSystemId(ds);
            StringBuffer si = new StringBuffer();
            for (int i = 0; i < id.length; i++) {
                if (i != 0) {
                    si.append(" and ");
                }
                si.append(" id != '" + id[i] + "' ");
            }

            StringBuffer sb = new StringBuffer();
            sb.append("select id,name from role where " + si.toString() + " order by name ");

            //System.out.print("SQL "+sb.toString());
            ResultSet rs = stmt.executeQuery(sb.toString());
            while (rs.next()) {
                SelectItem item = new SelectItem();
                item.setKey(rs.getString(1));
                String value = rs.getString(2);
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    private String[] getRoleSystemId(DataSource ds) {
        String[] id = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select distinct(role_id) from role_systems order by role_id");
            ResultSet rs = stmt.executeQuery(sb.toString());

            List<String> l = new ArrayList<String>();
            while (rs.next()) {
                l.add(rs.getString(1).trim());
            }
            rs.close();
            stmt.close();
            conn.close();

            id = new String[l.size()];
            for (int i = 0; i < l.size(); i++) {
                id[i] = l.get(i);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public List<SelectItem> getAllSystems(DataSource ds) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select id,name from systems order by name ");
            while (rs.next()) {
                SelectItem item = new SelectItem();
                String key = rs.getString(1);
                String value = rs.getString(2);
                item.setKey((key == null) ? "" : key.trim());
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<SelectItem> getSystemsByEnable(DataSource ds,String enable) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        try {
            StringBuffer sb = new StringBuffer();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            sb.append("select id,name from systems ");
            if(enable.trim().length()>0){
                sb.append("where enable='"+enable+"' ");
            }
            sb.append("order by name ");
            ResultSet rs = stmt.executeQuery(sb.toString());

            while (rs.next()) {
                SelectItem item = new SelectItem();
                String key = rs.getString(1);
                String value = rs.getString(2);
                item.setKey((key == null) ? "" : key.trim());
                item.setValue((value == null) ? "" : value.trim());
                list.add(item);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public String[] getSystemsIdByRoleId(DataSource ds, String role_id) {
        List<String> list = new ArrayList<String>();
        String[] id = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select systems_id from role_systems where role_id = '" + role_id.trim() + "' ");
            ResultSet rs = stmt.executeQuery(sb.toString());

            while (rs.next()) {
                list.add(rs.getString(1));
            }
            rs.close();
            stmt.close();
            conn.close();

            if (list.size() > 0) {
                id = new String[list.size()];
                for (int i = 0; i < list.size(); i++) {
                    id[i] = list.get(i);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }
    public String[] getEmailByRoleId(DataSource ds,int[] roleId) {
        List<String> list = new ArrayList<String>();
        
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            sb.append("select email from users where role_id in ( ");
            for(int i=0;i<roleId.length;i++){
                if(i!=0)sb.append(",");
                sb.append("'"+roleId[i]+"'");
            }
            sb.append(") ");
            ResultSet rs = stmt.executeQuery(sb.toString());

            while (rs.next()) {
                list.add(rs.getString(1));
            }
            rs.close();
            stmt.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return list.toArray(new String[list.size()]);
    }

    public void updateControlPanel(HttpSession session, DataSource ds, String role_id) {
        List<UserControl> list = new ArrayList<UserControl>();
        list = getUserControl(ds, role_id);
        //System.out.println("Size " + list.size());
        if (list.size() > 0) {
            session.setAttribute(Token.USER_CONTROL, list);
        }
    }

    public Users signinCheck(DataSource ds, String user_name, String password) {//have one
        StringBuffer sql = new StringBuffer();
        //sql.append("Select *,get_rolename(role_id) From Users Where user_name='" + user_name.trim() + "' and password='" + password.trim() + "' and enable='1' ");
        sql.append("Select *,(select name from role where id=role_id) as ROLE_NAME From users Where user_name='" + user_name.trim() + "' and password=MD5('" + password.trim() + "') and enable='1' ");
        System.out.println("signinCheck: " + sql.toString());
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                
                o.setCreateBy(rs.getString("CREATE_BY"));

                o.setRoleName(rs.getString("ROLE_NAME"));

                if (o.getPreviewImage().length() <= 0) {
                    if (o.getGender().toUpperCase().equals("M")) {
                        o.setPreviewImage("images/male.jpg");
                    } else {
                        o.setPreviewImage("images/female.jpg");
                    }
                }

            }
            rs.close();
            stmt.close();
            conn.close();
            return o;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Users getUsersessionData(DataSource ds, String id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select *,(select name from role where role_id=id) as role_name From Users Where id='" + id.trim() + "' ");
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(rs.getString(1));
                o.setUserName(rs.getString(2));
                o.setPassword(rs.getString(3));
                o.setName(rs.getString(4));
                o.setEmail(rs.getString(5));
                o.setPhone(rs.getString(6));
                o.setGender(rs.getString(7));
                o.setCreateDate(rs.getString(8));
                o.setLastLogin(rs.getString(9));
                o.setEnable(rs.getString(10));
                o.setRoleId(rs.getString(11));
                o.setRoleName(rs.getString(12));

            }
            rs.close();
            stmt.close();
            conn.close();
            return o;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean updateLastLogin(DataSource ds, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET last_login=?,status=? where id='" + id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setTimestamp(1, func.getTimestamp());
            stmt.setString(2, Integer.toString(Config.USER_STATUS_ONLINE));
            stmt.executeUpdate();
            stmt.close();
            ////conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updateOfflineStatus(DataSource ds, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET status=? where id='" + id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, Integer.toString(Config.USER_STATUS_OFFLINE));
            stmt.executeUpdate();
            stmt.close();
            ////conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public String getUrlFullContextPath(HttpServletRequest request) {
        URL fullContext = null;
        try {
            fullContext = new URL(request.getScheme(), request.getServerName(), request.getServerPort(), request.getContextPath());
        } catch (MalformedURLException ex) {
            Logger.getLogger(Service.class.getName()).log(Level.SEVERE, null, ex);
        }
        return fullContext.toString();
    }
    public List<SelectItem> getUploadImageTableIdList(String currentID, boolean showSelectAll) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        SelectItem si = new SelectItem();
        if (showSelectAll) {
            si = new SelectItem();
            si.setKey("");
            si.setValue("--- All Category ---");
            list.add(si);
        }

        for (int i = 0; i < Config.UPLOAD_TABLE_ID_LIST.length; i++) {
            si = new SelectItem();
            si.setKey(Config.UPLOAD_TABLE_ID_LIST[i].split(":")[0]);
            si.setValue(Config.UPLOAD_TABLE_ID_LIST[i].split(":")[1]);
            if (currentID.equals(Config.UPLOAD_TABLE_ID_LIST[i].split(":")[0])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }
    public List<SelectItem> getUploadImageTypeList(String currentID) {
        List<SelectItem> list = new ArrayList<SelectItem>();
        for (int i = 0; i < Config.IMG_TYPE_LIST.length; i++) {
            SelectItem si = new SelectItem();
            si.setKey(Config.IMG_TYPE_LIST[i].split(":")[0]);
            si.setValue(Config.IMG_TYPE_LIST[i].split(":")[1]);
            if (currentID.equals(Config.IMG_TYPE_LIST[i].split(":")[0])) {
                si.setSelect(true);
            }
            list.add(si);
        }
        return list;
    }
}
